package wiki;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

import org.json.simple.JSONArray;

import com.Root;

public class getReports {

	public Map<String, String> getYearlyReports() {
		Root root = new Root();
		ResultSet rs = null;
		PreparedStatement pst = null;
		String sql = "";
		
		Map<String, String> reportdetails = new HashMap<String, String>();

		//System.out.println("Issueid: : " + issueid);

		try {
			root.initConnection();

			sql = "select createdon, count(createdon) as count ,month(createdon) as month, year(createdon) as year from issue group by month(createdon) order by year(createdon) desc, month(createdon ) desc limit 20;" ;


			pst = root.con.prepareStatement(sql);
			rs = pst.executeQuery();

			//System.out.println("PST: " + pst);
			rs.next();
			String t1 = "";
			String t2 ="";
			
			JSONArray reportlist;
			
			reportlist = new JSONArray();
			
			reportlist.add("count", rs.getString("count"));
			reportdetails.put("month", rs.getString("month"));
			reportdetails.put("year", rs.getString("year"));
			
			while (rs.next()) {
				t2 = rs.getString("month");
				if ( Integer.parseInt(t1) -1 == Integer.parseInt(t2) ) {
					reportdetails.put("count", rs.getString("count"));
					reportdetails.put("month", t2);
					reportdetails.put("year", rs.getString("year"));
					
				}else{
					reportdetails.put("count", "0");
					reportdetails.put("month", "0");
					reportdetails.put("year", "0");
				}
				
								
				System.out.println("ReportDetails: " + reportdetails.toString()) ;
			}
			

		} catch (Exception e) {
			System.out.println("Error reading report details " + e.toString());
		} finally {
			try {
				rs.close();
				pst.close();
				
				root.closeConnection();
			} catch (Exception e) {

			}

		}
		return (reportdetails);
	}

	
	
}
